package org.benben;

import java.sql.*;
import java.util.*;

public class DataBase 
{
    private String dbURL;
    private String dbDriver;
    private String dbUser;
    private String dbPassword;
    private String dbSchema;
    private String dbCatalog = "";
    private Connection dbConnection;
    private int dbConnecTimeout = 1000;
    private boolean dbStatus = false;
    private boolean dbAutoCommit = true;
    private PreparedStatement dbStatement;
    private ResultSet dbResultSet;
    private DatabaseMetaData dbMetaData;
    private int batchSize = 1000;
        
    public DataBase(String dbDriver, String dbURL, String dbUser, String dbPassword)
    {
        this.dbDriver = dbDriver;
        this.dbURL = dbURL;
        this.dbUser = dbUser;
        this.dbPassword = dbPassword;
        
        try
        {
            Class.forName(this.dbDriver);
        }catch(ClassNotFoundException ex)
        {
            ex.printStackTrace();
            this.dbStatus = false;
        }
    }
    
    public DataBase(String dbDriver, String dbURL, String dbUser, String dbPassword, String dbSchema)
    {
        this.dbDriver = dbDriver;
        this.dbURL = dbURL;
        this.dbUser = dbUser;
        this.dbPassword = dbPassword;
        this.dbSchema = dbSchema;
        
        try
        {
            Class.forName(this.dbDriver);
        }catch(ClassNotFoundException ex)
        {
            ex.printStackTrace();
            this.dbStatus = false;
        }
    }
    
    public DataBase(Properties dbProperty)
    {
        this.dbDriver = dbProperty.getProperty("dbDriver", "");
        this.dbURL = dbProperty.getProperty("dbURL", "");
        this.dbUser = dbProperty.getProperty("dbUser", "");
        this.dbPassword = dbProperty.getProperty("dbPassword", "");
        
        try
        {
            Class.forName(this.dbDriver);
        }catch(ClassNotFoundException ex)
        {
            ex.printStackTrace();
            this.dbStatus = false;
        }
    }
    
    public DataBase(HashMap<String, String> dbProperty)
    {
        if(dbProperty.containsKey("dbDriver") == true)
            this.dbDriver = dbProperty.get("dbDriver");
        else
            this.dbDriver = "";
        
        if(dbProperty.containsKey("dbURL") == true)
            this.dbURL = dbProperty.get("dbURL");
        else 
            this.dbURL = "";
        
        if(dbProperty.containsKey("dbUser") == true)
            this.dbUser = dbProperty.get("dbUser");
        else
            this.dbUser = "";
        
        if(dbProperty.containsKey("dbPassword") == true)
            this.dbPassword = dbProperty.get("dbPassword");
        else
            this.dbPassword = "";
        
        try
        {
            Class.forName(this.dbDriver);
        }catch(ClassNotFoundException ex)
        {
            ex.printStackTrace();
            this.dbStatus = false;
        }
    }
    
    public boolean connect()
    {
        this.dbStatus = true;
        
        try
        {
            this.dbConnection = DriverManager.getConnection(this.dbURL, this.dbUser, this.dbPassword);
            this.dbMetaData = dbConnection.getMetaData();
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            this.dbStatus = false;
        }
        finally
        {
            return this.dbStatus;
        }
    }
    
    public void setBatchSize(int batchSize)
    {
        this.batchSize = batchSize;
    }
    
    public int getBatchSize()
    {
        return this.batchSize;
    }
    
    public void setSchema(String dbSchema)
    {
        this.dbSchema = dbSchema;
        
        try
        {
            dbConnection.setSchema(this.dbSchema);
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            this.dbStatus = false;
        }
    }
    
    public String getSchema()
    {
        return this.dbSchema;
    }
    
    public void setAutoCommit(boolean dbAutoCommit)
    {
        this.dbAutoCommit = dbAutoCommit;

        try
        {
            dbConnection.setAutoCommit(this.dbAutoCommit);
        }catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            this.dbStatus = false;
        }
    }
    
    public boolean getAutoCommit()
    {
        return this.dbAutoCommit;
    }
    
    public String getCatalog()
    {
        String result = "";
        try
        {
            result = dbConnection.getCatalog();
        }catch(SQLException ex)
        {
            ex.printStackTrace();
        }finally
        {
            if(result == null)
                result = "";
            
            return result;
        }   
    }
    
    public int getFieldType(String catalog, String schema, String tableName, String fieldName)
    {
        int dataType = -1;
        
        try
        {
            ResultSet resultSet = dbMetaData.getColumns(catalog, schema, tableName, fieldName);
            //ResultSet resultSet = dbMetaData.getAttributes(catalog, schema, tableName, fieldName);
            //.out.println("catalog:" + catalog);
            //System.out.println("schema:" + schema);
           // System.out.println("table:" + tableName);
            //System.out.println("field:" + fieldName);
           // if(resultSet == null)
           //     System.out.println("result null");
           // else
          //      System.out.println("result not null");
            while(resultSet.next())
                dataType =  resultSet.getInt("DATA_TYPE");
            //System.out.println("data_type:" + dataType);
           // System.out.println("int:" + Types.INTEGER);
            //System.out.println("char:" + Types.CHAR);
            //System.out.println("float:" + Types.FLOAT);
            //System.out.println("double:" + Types.DOUBLE);
            //System.out.println("varchar:" + Types.VARCHAR);
            //System.out.println("timestamp:" + Types.TIMESTAMP);
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            dataType = -1;
        }
        finally
        {
            return dataType;
        }       
    }
    
    public boolean executeInsert(String sql)
    {
        boolean result = true;
        
        try
        {
            dbStatement = dbConnection.prepareStatement(sql);
            dbStatement.execute();
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            result = false;
        }finally
        {
            return result;
        }
    }
    
    public boolean executeInsert(String [] sql)
    {
        boolean result = true;
        
        try
        {
            boolean isAutoCommit = this.getAutoCommit();
            this.setAutoCommit(false);
            
            int sqlCount = sql.length;
            int currentBatchSize = 1;
            
            dbStatement = dbConnection.prepareStatement("");
            for(int i = 0;i < sqlCount;++i)
            {
                dbStatement.addBatch(sql[i]);
                ++currentBatchSize;
                
                if(currentBatchSize == this.getBatchSize())
                {
                    dbStatement.executeBatch();
                    dbConnection.commit();
                    currentBatchSize = 0;
                }
            }
            
            if(currentBatchSize > 0)
            {
                dbStatement.executeBatch();
                dbConnection.commit();
            }
            
            dbStatement.close();
            
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            result = false;
        }finally
        {
            return result;
        }
    }
    
    public String generateInsertSQL(String tableName, String [] fieldName, String[] values)
    {
        StringBuilder stringBuilder = new StringBuilder();
        String sql = "";
        
        int columnCount = fieldName.length;
        int [] columnType = new int[columnCount];
        for(int i = 0;i < columnCount;++i)
            columnType[i] =  this.getFieldType(this.getCatalog(), this.getSchema(), tableName, fieldName[i]);
        
        stringBuilder.append("INSERT INTO ");
        String temp = "\"" + tableName + "\"";
        stringBuilder.append(temp);
        stringBuilder.append("(");
        
        for(int columnIndex = 0;columnIndex < columnCount;++columnIndex)
        {
            stringBuilder.append("\"");
            stringBuilder.append(fieldName[columnIndex]);
            stringBuilder.append("\"");
            if(columnIndex != (columnCount - 1))
                stringBuilder.append(",");
        }
        stringBuilder.append(")");
        stringBuilder.append(" VALUES(");
        
        for(int i = 0;i < columnCount;++i)
        {
            int type = columnType[i];
            
            if(type == java.sql.Types.VARCHAR || type == SqlDataType.CHAR || type == SqlDataType.TIMESTAMP)
                temp = "'" + values[i] + "'";
            else
                temp = values[i];
            
            stringBuilder.append(temp);
            
            if(i != (columnCount - 1))
                stringBuilder.append(",");
        }
        
        stringBuilder.append(")");
        
        sql = stringBuilder.toString();
        
        return sql;
    }
    
    //public String generateUpdateSQL(String tableName, String [][] conditions, String [] fields, String[] values)
    public String generateUpdateSQL(String tableName, String[][] conditions, HashMap<String, String> keyValue)
    {
        String sql = "";
        String temp = "";
        StringBuilder stringBuilder = new StringBuilder();
        
        stringBuilder.append("UPDATE ");
        temp = "\"" + tableName + "\" ";
        stringBuilder.append(temp);
        stringBuilder.append(" SET ");
        
        int columnCount = keyValue.size();
        int type = 0;
        
        Set<String> keySet = keyValue.keySet();
        int i = 0;
        for(String key : keySet)
        {
            ++i;
            
            stringBuilder.append(key);
            stringBuilder.append(" = ");
            String value = keyValue.get(key);
            type = this.getFieldType(this.getCatalog(), this.getSchema(), tableName, key);
            
            if(type == SqlDataType.CHAR || type == SqlDataType.TIMESTAMP || type == SqlDataType.VARCHAR)
                temp = "'" + value + "'";
            else
                temp = value;
            
            stringBuilder.append(temp);
            if(i != columnCount)
                stringBuilder.append(",");
        }
        stringBuilder.append(" WHERE ");
        
        int conditionCount = conditions.length;
        
        for(i = 0;i < conditionCount;++i)
        {
            String field = "\"" + conditions[i][0] + "\" ";
            stringBuilder.append(field);
            String condition = conditions[i][1];
            stringBuilder.append(condition);
            
            if(i != (conditionCount - 1))
                stringBuilder.append(" and ");
        }
        
        sql = stringBuilder.toString();
        
        return sql;
    }
    
    public boolean executeInsert(String tableName, HashMap<String, String> keyValue)
    {
        int columnCount = keyValue.size();
        boolean result = true;
        
        String [] field = new String[columnCount];
        String [] values = new String[columnCount];
        
        Set<String> keySet = keyValue.keySet();
        
        int i = 0;
        for(String key : keySet)
        {
            String value = keyValue.get(key);
            field[i] = key;
            values[i] = value;
            ++i;
        }
        
        int [] columnType = new int[columnCount];
        for(i = 0;i < columnCount;++i)
            columnType[i] = this.getFieldType(this.getCatalog(), this.getCatalog(), tableName, field[i]);
        
        StringBuilder stringBuilder = new StringBuilder();
        String sql = "";
        
        stringBuilder.append("INSERT INTO ");
        String temp = "`" + tableName + "`";
        stringBuilder.append(temp);
        stringBuilder.append("(");
        for(int columnIndex = 0;columnIndex < columnCount;++columnIndex)
        {
            stringBuilder.append(" ");
            stringBuilder.append(field[columnIndex]);
            stringBuilder.append(" ");
            if(columnIndex != (columnCount - 1))
                stringBuilder.append(",");
        }
        stringBuilder.append(")");
        stringBuilder.append(" VALUES(");
        for(int columnIndex = 0;columnIndex < columnCount;++columnIndex)
        {
            stringBuilder.append("?");
            if(columnIndex != (columnCount - 1))
                stringBuilder.append(",");
        }
        stringBuilder.append(")");
        sql = stringBuilder.toString();
        
        try
        {
            dbStatement = dbConnection.prepareStatement(sql);
            
            for(int column = 0;column < columnCount;++column)
            {
                int dataType = columnType[column];
                switch(dataType)
                {
                    case Types.INTEGER:
                        dbStatement.setInt(column + 1, Integer.parseInt(values[column]));
                        break;
                    case Types.FLOAT:
                        dbStatement.setFloat(column + 1, Float.parseFloat(values[column]));
                        break;
                    case Types.DOUBLE:
                        dbStatement.setDouble(column + 1, Double.parseDouble(values[column]));
                        break;
                    case Types.VARCHAR:
                        dbStatement.setString(column + 1, values[column]);
                        break;
                    case Types.TIMESTAMP:
                        dbStatement.setTimestamp(column + 1, Timestamp.valueOf(values[column]));
                        break;
                    case Types.CHAR:
                        dbStatement.setString(column + 1, values[column]);
                        break;
                    default:
                        dbStatement.setString(column + 1, values[column]);

                        //throw new SQLException();
                }             
            }
            
            result = dbStatement.execute();
        }catch(SQLException ex)
        {
            result = false;
        }finally
        {
            return result;
        }
    }
    
    public boolean executeInsert(String tableName, String [] fieldName, String[][]values)
    {
        boolean result = true;
        StringBuilder stringBuilder;
        String sql;
       
        try
        {
            boolean isAutoCommit = this.getAutoCommit();
            this.setAutoCommit(false);
            
            int columnCount = fieldName.length;
            int recordCount = values.length;
            stringBuilder = new StringBuilder();
            
            stringBuilder.append("INSERT INTO ");
            String temp = "`" + tableName + "`";
            stringBuilder.append(temp);
            stringBuilder.append("(");
            for(int columnIndex = 0;columnIndex < columnCount;++columnIndex)
            {
                 stringBuilder.append(" ");
                 stringBuilder.append(fieldName[columnIndex]);
                 stringBuilder.append(" ");
                 if(columnIndex != (columnCount - 1))
                     stringBuilder.append(",");
            }
            stringBuilder.append(")");
            stringBuilder.append(" VALUES(");
            for(int columnIndex = 0;columnIndex < columnCount;++columnIndex)
            {
                stringBuilder.append("?");
                if(columnIndex != (columnCount - 1))
                    stringBuilder.append(",");
            }
            stringBuilder.append(")");
            sql = stringBuilder.toString();
            dbStatement = dbConnection.prepareStatement(sql);
            
            int currentBatchSize = 0;
            
            int [] columnType = new int[columnCount];
            for(int i = 0;i < columnCount;++i)
                columnType[i] =  this.getFieldType(this.getCatalog(), this.getCatalog(), tableName, fieldName[i]);
            
            for(int row = 0;row < recordCount;++row)
            {                
                for(int column = 0;column < columnCount;++column)
                {
                    int dataType = columnType[column];
                    switch(dataType)
                    {
                        case Types.INTEGER:
                            dbStatement.setInt(column + 1, Integer.parseInt(values[row][column]));
                            break;
                        case Types.FLOAT:
                            dbStatement.setFloat(column + 1, Float.parseFloat(values[row][column]));
                            break;
                        case Types.DOUBLE:
                            dbStatement.setDouble(column + 1, Double.parseDouble(values[row][column]));
                            break;
                        case Types.VARCHAR:
                            dbStatement.setString(column + 1, values[row][column]);
                            break;
                        case Types.TIMESTAMP:
                            dbStatement.setTimestamp(column + 1, Timestamp.valueOf(values[row][column]));
                            break;
                        case Types.CHAR:
                            dbStatement.setString(column + 1, values[row][column]);
                            break;
                        default:
                            dbStatement.setString(column + 1, values[row][column]);

                            //throw new SQLException();
                    }             
                }
                
                dbStatement.addBatch();    
                ++currentBatchSize;
                if(currentBatchSize == batchSize)
                {    
                    dbStatement.executeBatch();
                    dbConnection.commit();
                    currentBatchSize = 0;
                }                    
            }
            
            if(currentBatchSize > 0)
            {
                int [] res = dbStatement.executeBatch();
                dbConnection.commit();
            }
            
            dbStatement.close();
            
            dbConnection.setAutoCommit(isAutoCommit);
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            result = false;
        }
        finally
        {
            return result;
        }
    }
    
    public ResultSet executeQuery(String sql)
    {
        try
        {
            dbStatement = dbConnection.prepareStatement(sql);
            dbResultSet = dbStatement.executeQuery();        
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            dbResultSet = null;
        }
        finally
        {
            return dbResultSet;
        }
    }
    
    public void closeResult()
    {
        try
        {
            dbResultSet.close();
        }catch(SQLException ex)
        {
            ex.printStackTrace();
        }
    }
    
    public ResultSet executeQuery(String[] tables, String[] fields, String[] conditions)
    {      
        return null;
    }
    
    public ResultSet executeQuery(String tableName, String [] fields, String[] conditions)
    {
        StringBuilder stringBuilder = new StringBuilder();
        
        stringBuilder.append("SELECT ");
        
        int count = fields.length;
        for(int i = 0;i < count;++i)
        {
            stringBuilder.append(fields[i]);
            if(i != (count - 1))
                stringBuilder.append(",");
        }
        
        count = conditions.length;
        
        stringBuilder.append(" FROM ");
        stringBuilder.append(tableName);
        stringBuilder.append(" WHERE ");
        
        for(int i = 0;i < count;++i)
        {
            stringBuilder.append(conditions[i]);
            if(i != (count - 1))
                stringBuilder.append(" AND ");
        }
        
        String sql = stringBuilder.toString();
        System.out.println(sql);
        try
        {
            dbStatement = dbConnection.prepareStatement(sql);
            dbResultSet = dbStatement.executeQuery();
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            dbResultSet = null;
        }finally
        {
            return dbResultSet;
        }
        
    }
    
    public boolean executeUpdate(String sql)
    {
        boolean result = true;
        
        try
        {
            dbStatement = dbConnection.prepareStatement(sql);
            dbStatement.execute();
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            result = false;
        }finally
        {
            return result;
        }
    }
    
    public boolean execueteUpdate(String [] sql)
    {
        boolean result = true;
        
        try
        {
            boolean isAutoCommit = this.getAutoCommit();
            this.setAutoCommit(false);
            
            int sqlCount = sql.length;
            int currentBatchSize = 0;
            
            dbStatement = dbConnection.prepareStatement("");
            for(int i = 0;i < sqlCount;++i)
            {
                dbStatement.addBatch(sql[i]);
                ++currentBatchSize;
                
                if(currentBatchSize == this.getBatchSize())
                {
                    dbStatement.executeBatch();
                    dbConnection.commit();
                    currentBatchSize = 0;
                }
            }
            
            if(currentBatchSize > 0)
            {
                dbStatement.executeBatch();
                dbConnection.commit();
            }
            
            dbStatement.close();
            this.setAutoCommit(isAutoCommit);
            
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            result = false;
        }finally
        {
            return result;
        }
    }
    
    public boolean executeUpdate(String tableName, String [] fields, String [] values)
    {
        StringBuilder stringBuilder = new StringBuilder();
        String sql = "";
        boolean result = true;
        
        int columnCount = fields.length;
        
        stringBuilder.append("UPDATE ");
        String temp = "`" + tableName + "`";
        stringBuilder.append(temp);
        stringBuilder.append(" SET ");
        for(int i = 0;i < columnCount;++i)
        {
            stringBuilder.append(fields[i]);
            stringBuilder.append(" = ? ");
            if(i != (columnCount - 1))
                stringBuilder.append(",");
        }
        
        sql = stringBuilder.toString();
        
        
        int [] columnType = new int[columnCount];
        for(int i = 0;i < columnCount;++i)
            columnType[i] = this.getFieldType(this.getCatalog(), this.getCatalog(), tableName, fields[i]);
        
        try
        {
            dbStatement = dbConnection.prepareStatement(sql);
            
            for(int col = 0;col < columnCount;++col)
            {
                int dataType = columnType[col];
                switch(dataType)
                {
                    case Types.INTEGER:
                        dbStatement.setInt(col + 1, Integer.parseInt(values[col]));
                        break;
                    case Types.FLOAT:
                        dbStatement.setFloat(col + 1, Float.parseFloat(values[col]));
                        break;
                    case Types.DOUBLE:
                        dbStatement.setDouble(col + 1, Double.parseDouble(values[col]));
                        break;
                    case Types.VARCHAR:
                        dbStatement.setString(col + 1, values[col]);
                        break;
                    case Types.TIMESTAMP:
                        dbStatement.setTimestamp(col + 1, Timestamp.valueOf(values[col]));
                        break;
                    default:
                        throw new SQLException();
                }             
            }
            
            result = dbStatement.execute();
            
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            result = false;
        }finally
        {
            return result;
        }     
    }
    
    public boolean executeUpdate(String tableName, HashMap<String, String> keyValue)
    {
        int columnCount = keyValue.size();
        
        String [] field = new String[columnCount];
        String [] values = new String[columnCount];
        
        Set<String> keySet = keyValue.keySet();
        
        int i = 0;
        for(String key : keySet)
        {
            String value = keyValue.get(key);
            field[i] = key;
            values[i] = value;
            ++i;
        }
        
        return this.executeUpdate(tableName, field, values);
    }
    
    public boolean execueteUpdate(String tableName, String [][] conditions, String [] fields, String[][] values)
    {
        return false;
    }
    
    public boolean executeUpdate(String tableName, String [] fields, String [][] values)
    {
        StringBuilder stringBuilder;
        String sql = "";
        String temp = "";
        boolean result = true;
        
        boolean isAutoCommit = this.getAutoCommit();
        this.setAutoCommit(false);
        
        int rowCount = values.length;
        int columnCount = fields.length;
        
        int [] columnType = new int[columnCount];
        
        for(int i = 0;i < columnCount;++i)
            columnType[i] = this.getFieldType(this.getCatalog(), this.getCatalog(), tableName, fields[i]);
        
        stringBuilder = new StringBuilder();
        stringBuilder.append("UPDATE ");
        temp = "`" + tableName + "` ";
        stringBuilder.append(temp);
        stringBuilder.append(" SET ");
        
        for(int col = 0;col < columnCount;++col)
        {
            String field = fields[col];
            stringBuilder.append(field);
            stringBuilder.append(" = ? ");
            if(col != (columnCount - 1))
                stringBuilder.append(" , ");
        }
        
        sql = stringBuilder.toString();
        
        try
        {
            dbStatement = dbConnection.prepareStatement(sql);
            
            int currentBatchSize = 0;
            
            for(int row = 0;row < rowCount;++row)
            {
                ++currentBatchSize;
                
                for(int col = 0;col < columnCount;++col)
                {
                    int dataType = columnType[col];
                    switch(dataType)
                    {
                        case Types.INTEGER:
                            dbStatement.setInt(col + 1, Integer.parseInt(values[row][col]));
                            break;
                        case Types.FLOAT:
                            dbStatement.setFloat(col + 1, Float.parseFloat(values[row][col]));
                            break;
                        case Types.DOUBLE:
                            dbStatement.setDouble(col + 1, Double.parseDouble(values[row][col]));
                            break;
                        case Types.VARCHAR:
                            dbStatement.setString(col + 1, values[row][col]);
                            break;
                        case Types.TIMESTAMP:
                            dbStatement.setTimestamp(col + 1, Timestamp.valueOf(values[row][col]));
                            break;
                        default:
                            throw new SQLException();
                    }             
                }
                
                dbStatement.addBatch();
                if(currentBatchSize == this.getBatchSize())
                {
                    dbStatement.executeBatch();
                    dbConnection.commit();
                    currentBatchSize = 0;
                }
            }
            
            if(currentBatchSize > 0)
            {
                dbStatement.executeBatch();
                dbConnection.commit();
            }
            
            dbStatement.close();
            this.setAutoCommit(isAutoCommit);
            
        }catch(SQLException ex)
        {
            ex.printStackTrace();
            result = false;
        }finally
        {
            return result;
        }
    }
}